﻿Imports System.Text

Namespace StatementSources
    Public Class CreateEmployeeStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeCode As String, firstName As String, middleName As String, lastName As String, homeAddress As String, contactNumber As String, emailAddress As String, courseId As Integer, employeeTypeId As Integer, year As Integer, createdBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("INSERT [dbo].[Employees] ")
            stringBuilder.Append("([EmployeeCode],[FirstName],[MiddleName],[LastName],[HomeAddress],[ContactNumber],[EmailAddress],[CourseId],[EmployeeTypeId],[Year],[IsActive],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
            stringBuilder.Append("VALUES (@EmployeeCode,@FirstName,@MiddleName,@LastName,@HomeAddress,@ContactNumber,@EmailAddress,@CourseId,@EmployeeTypeId,@Year,@IsActive,@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeCode", employeeCode)
            Parameters.AddWithValue("@FirstName", firstName)
            Parameters.AddWithValue("@MiddleName", middleName)
            Parameters.AddWithValue("@LastName", lastName)
            Parameters.AddWithValue("@HomeAddress", homeAddress)
            Parameters.AddWithValue("@ContactNumber", contactNumber)
            Parameters.AddWithValue("@EmailAddress", emailAddress)
            Parameters.AddWithValue("@CourseId", courseId)
            Parameters.AddWithValue("@EmployeeTypeId", employeeTypeId)
            Parameters.AddWithValue("@Year", year)
            Parameters.AddWithValue("@IsActive", True)
            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub

    End Class

    Public Class ReadAllEmployeeStatementSource
        Inherits BaseStatementSource

        Public Sub New()
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[FirstName],[MiddleName],[LastName],[HomeAddress],[ContactNumber],[EmailAddress],[CourseId],[EmployeeTypeId],[EmployeeCode],[Year],[IsActive],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Employees];")

            Sql = stringBuilder.ToString()
        End Sub

    End Class

    Public Class ReadAllEmployeeByScheduleIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(scheduleId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT a.[EmployeeId],a.[FirstName],a.[MiddleName],a.[LastName],a.[HomeAddress],a.[ContactNumber],a.[EmailAddress],a.[CourseId],a.[EmployeeTypeId],a.[EmployeeCode],a.[Year],a.[IsActive],a.[CreatedBy],a.[CreatedDttm],a.[ModifiedBy],a.[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Employees] a ")
            stringBuilder.Append("INNER JOIN [dbo].[Matriculations] b ")
            stringBuilder.Append("ON a.[EmployeeId]=b.[EmployeeId] ")
            stringBuilder.Append("WHERE b.[ScheduleId]=@ScheduleId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub

    End Class

    Public Class ReadByEmployeeIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[FirstName],[MiddleName],[LastName],[HomeAddress],[ContactNumber],[EmailAddress],[CourseId],[EmployeeTypeId],[EmployeeCode],[Year],[IsActive],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Employees] ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
        End Sub

    End Class

    Public Class ReadEmployeeForCodeStatementSource
        Inherits BaseStatementSource

        Public Sub New(courseId As Integer, studentYear As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT COUNT(e.[EmployeeId]) As EmployeeCount ")
            stringBuilder.Append("FROM [dbo].[Employees] e, [dbo].[EmployeeTypes] et ")
            stringBuilder.Append("WHERE e.[CourseId]=@CourseId ")
            stringBuilder.Append("AND e.[Year]=@Year ")
            stringBuilder.Append("AND e.[EmployeeTypeId]=et.[EmployeeTypeId];")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@CourseId", courseId)
            Parameters.AddWithValue("@Year", studentYear)
        End Sub

    End Class

    Public Class ReadByEmployeeCodeStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeCode As String)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[FirstName],[MiddleName],[LastName],[HomeAddress],[ContactNumber],[EmailAddress],[CourseId],[EmployeeTypeId],[EmployeeCode],[Year],[IsActive],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Employees] ")
            stringBuilder.Append("WHERE [EmployeeCode]=@EmployeeCode ")
            stringBuilder.Append("AND [IsActive]=@IsActive ")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeCode", employeeCode)
            Parameters.AddWithValue("@IsActive", True)
        End Sub

    End Class

    Public Class UpdateEmployeeStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, _
                       firstName As String, _
                       middleName As String, _
                       lastName As String, _
                       homeAddress As String, _
                       contactNumber As String, _
                       emailAddress As String, _
                       courseId As Integer, _
                       employeeTypeId As Integer, _
                       employeeCode As String, _
                       year As Integer, _
                       modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("UPDATE [dbo].[Employees] SET ")
            stringBuilder.Append("[FirstName]=ISNULL(@FirstName,[FirstName]),")
            stringBuilder.Append("[MiddleName]=ISNULL(@MiddleName,[MiddleName]),")
            stringBuilder.Append("[LastName]=ISNULL(@LastName,[LastName]),")
            stringBuilder.Append("[HomeAddress]=ISNULL(@HomeAddress,[HomeAddress]),")
            stringBuilder.Append("[ContactNumber]=ISNULL(@ContactNumber,[ContactNumber]),")
            stringBuilder.Append("[EmailAddress]=ISNULL(@EmailAddress,[EmailAddress]),")
            stringBuilder.Append("[CourseId]=ISNULL(@CourseId,[CourseId]),")
            stringBuilder.Append("[EmployeeTypeId]=ISNULL(@EmployeeTypeId,[EmployeeTypeId]),")
            stringBuilder.Append("[EmployeeCode]=ISNULL(@EmployeeCode,[EmployeeCode]),")
            stringBuilder.Append("[Year]=ISNULL(@Year,[Year]),")
            stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
            stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@FirstName", firstName)
            Parameters.AddWithValue("@MiddleName", middleName)
            Parameters.AddWithValue("@LastName", lastName)
            Parameters.AddWithValue("@HomeAddress", homeAddress)
            Parameters.AddWithValue("@ContactNumber", contactNumber)
            Parameters.AddWithValue("@EmailAddress", emailAddress)
            Parameters.AddWithValue("@CourseId", courseId)
            Parameters.AddWithValue("@EmployeeTypeId", employeeTypeId)
            Parameters.AddWithValue("@EmployeeCode", employeeCode)
            Parameters.AddWithValue("@Year", year)
            Parameters.AddWithValue("@IsActive", True)
            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub

    End Class

    Public Class DeleteEmployeeStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer)
            Sql = "UPDATE [dbo].[Employees] SET [IsActive]=ISNULL(@IsActive,[IsActive]) WHERE [EmployeeId]=@EmployeeId;"
            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@IsActive", False)
        End Sub

    End Class

End Namespace

